package com.ruoyi.model.service.impl;

import java.sql.ResultSet;
import java.util.*;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.PageHelper;
import com.ruoyi.common.core.utils.DateUtils;
import com.ruoyi.common.core.utils.StringUtils;
import com.ruoyi.common.core.utils.sql.SqlUtil;
import com.ruoyi.common.core.web.domain.AjaxResult;
import com.ruoyi.common.core.web.page.PageDomain;
import com.ruoyi.model.domain.EtlModelFields;
import com.ruoyi.model.mapper.EtlModelFieldsMapper;
import org.apache.hadoop.hive.metastore.partition.spec.CompositePartitionSpecProxy;
import org.aspectj.weaver.loadtime.Aj;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.ruoyi.model.mapper.EtlModelMapper;
import com.ruoyi.model.domain.EtlModel;
import com.ruoyi.model.service.IEtlModelService;

/**
 * 数据模型管理Service业务层处理
 * 
 * @author tangyongbo
 * @date 2021-05-16
 */
@Service
public class EtlModelServiceImpl implements IEtlModelService 
{
    private static final Logger log = LoggerFactory.getLogger(EtlModelServiceImpl.class);

    @Autowired
    @Qualifier("hiveJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private EtlModelMapper etlModelMapper;

    @Autowired
    private EtlModelFieldsMapper etlModelFieldsMapper;

    /**
     * 查询数据模型管理
     * 
     * @param id 数据模型管理ID
     * @return 数据模型管理
     */
    @Override
    public EtlModel selectEtlModelById(Long id)
    {
        return etlModelMapper.selectEtlModelById(id);
    }

    /**
     * 查询数据模型管理列表
     * 
     * @param etlModel 数据模型管理
     * @return 数据模型管理
     */
    @Override
    public List<EtlModel> selectEtlModelList(EtlModel etlModel)
    {
        return etlModelMapper.selectEtlModelList(etlModel);
    }

    /**
     * 新增数据模型管理
     * 
     * @param etlModel 数据模型管理
     * @return 结果
     */
    @Override
    public int insertEtlModel(EtlModel etlModel)
    {
        etlModel.setCreateTime(DateUtils.getNowDate());
        return etlModelMapper.insertEtlModel(etlModel);
    }

    /**
     * 修改数据模型管理
     * 
     * @param etlModel 数据模型管理
     * @return 结果
     */
    @Override
    public int updateEtlModel(EtlModel etlModel)
    {
        etlModel.setUpdateTime(DateUtils.getNowDate());
        return etlModelMapper.updateEtlModel(etlModel);
    }

    /**
     * 批量删除数据模型管理
     * 
     * @param ids 需要删除的数据模型管理ID
     * @return 结果
     */
    @Override
    public int deleteEtlModelByIds(Long[] ids)
    {
        return etlModelMapper.deleteEtlModelByIds(ids);
    }

    /**
     * 删除数据模型管理信息
     * 
     * @param id 数据模型管理ID
     * @return 结果
     */
    @Override
    public int deleteEtlModelById(Long id)
    {
        return etlModelMapper.deleteEtlModelById(id);
    }

    @Override
    public AjaxResult createTable(EtlModel etlModel){
    //    List<Map<String,Object>> res =
      //          jdbcTemplate.queryForList("select * from hive_table");
        String createTableSQL = "";
        try{
            String tableName = etlModel.getModelName();
            createTableSQL = getCreateTableSQLByModle(etlModel);
            String backupTableName = backupOldTable(tableName);
            log.info("建物理模型语句："+createTableSQL);
            jdbcTemplate.execute(createTableSQL);
            log.info("建物理模型语句:"+createTableSQL+"；执行成功！");
            return AjaxResult.success("创建物理表成功！");
        }catch(Exception e){
            e.printStackTrace();
            log.error(e.getMessage());
            return AjaxResult.error(e.getMessage());
        }
    }

    /**
     * 获取模型的数据
     *
     * @param etlModel
     * @return
     */
    @Override
    public List<Map<String,Object>> selectEtlModelDataList(EtlModel etlModel, PageDomain pageDomain) {
        String selTableSQL = "";
        Integer pageNum = pageDomain.getPageNum();
        Integer pageSize = pageDomain.getPageSize();
        List<EtlModelFields> fields = null;
        if (StringUtils.isNotNull(pageNum) && StringUtils.isNotNull(pageSize))
        {
            String orderBy = SqlUtil.escapeOrderBySql(pageDomain.getOrderBy());
            EtlModelFields modelfields = new EtlModelFields();
            modelfields.setModelId(etlModel.getId());
            fields = etlModelFieldsMapper.selectEtlModelFieldsList(modelfields);
            if(("".equals(orderBy))||(orderBy==null)){
                Iterator<EtlModelFields> rows = fields.iterator();
                EtlModelFields field = rows.next();
                selTableSQL = "select t11.* from "+
                        "( "+
                        "select row_number() over (order by t10."+field.getFieldName()+") as rnum ,t10.* "+
                        "from "+etlModel.getModelName()+"  t10 "+
                        ")t11 "+
                        "where t11.rnum  between "+(pageNum-1)*pageSize+ " and "+pageNum*pageSize;
            }else{
                selTableSQL = "select t11.* from "+
                        "( "+
                        "select row_number() over (order by t10."+orderBy+") as rnum ,t10.* "+
                        "from "+etlModel.getModelName()+"  t10 "+
                        ")t11 "+
                        "where t11.rnum  between "+(pageNum-1)*pageSize+ " and "+pageNum*pageSize;
            }
            //目前无法执行以上分页语句，用以下语句代替
            selTableSQL = "select * from "+etlModel.getModelName();
        }else{
            selTableSQL = "select * from "+etlModel.getModelName();
        }
        try{
            System.out.println("selTableSQL:"+selTableSQL);
      //      Object[] params2 = new Object[] {};
      //      List<Map<String,Object>> res = jdbcTemplate.queryForList(selTableSQL,params2);
            List<Map<String,Object>> temp_res = jdbcTemplate.queryForList(selTableSQL);
            Iterator<Map<String,Object>> rows = temp_res.iterator();
            List<Map<String,Object>> res = new ArrayList<>();
            while(rows.hasNext()){
                Map<String,Object> row = rows.next();
                Map<String,Object> target_row = new HashMap<>();
                Iterator<EtlModelFields> cols = fields.iterator();
                while(cols.hasNext()){
                    EtlModelFields field = cols.next();
                    target_row.put(field.getFieldName(),row.get(etlModel.getModelName()+"."+field.getFieldName()));
                }
                res.add(target_row);
            }
            return res;
        }catch(Exception e){
            e.printStackTrace();
            log.error(e.getMessage());
            return null;
        }
    }

    /**
     * 将属性信息列表转换为Map
     * @param fieldsList
     * @return
     */
    public Map<String,String> getDataTypesByFieldArray(JSONArray fieldsList){
        Map<String,String> resMap = new HashMap<>();
        for(int i=0;i<fieldsList.size();i++){
            JSONObject field = fieldsList.getJSONObject(i);
            resMap.put(field.getString("fieldName"),field.getString("dataType"));
        }
        return resMap;
    }

    public StringBuffer getFieldValue(String fieldKey,String fieldValue,Map<String,String> fieldsMap){
        StringBuffer res = new StringBuffer();
        String dataType = fieldsMap.get(fieldKey);
        if("varchar".equals(dataType)){
            res.append("'");
            res.append(fieldValue);
            res.append("'");
        }else if(("int".equals(dataType))||("float".equals(dataType))||("double".equals(dataType))||("decimals".equals(dataType))){
            res.append(fieldValue);
        }else{
            res.append("'");
            res.append(fieldValue);
            res.append("'");
        }
        return res;
    }

    /**
     * 新增模型数据
     *
     * @param etlModelData
     * @return
     */
    @Override
    public int insertEtlModelData(JSONObject etlModelData) {
        try{
            String modelName = etlModelData.getString("modelName");
            JSONObject dataObject = etlModelData.getJSONObject("_data");
            JSONArray fieldsList =  etlModelData.getJSONArray("_fields");
            Map<String,String> fieldDataTypes = getDataTypesByFieldArray(fieldsList);
            Iterator<String> keys = dataObject.keySet().iterator();
            StringBuffer insertFields = new StringBuffer();
            StringBuffer insertValues = new StringBuffer();
            while(keys.hasNext()){
                String nodeKey = keys.next();
                if(insertFields.length()>0){
                    insertFields.append(",");
                    insertValues.append(",");
                }
                insertFields.append(nodeKey);

                StringBuffer fieldValule = getFieldValue(nodeKey,dataObject.getString(nodeKey),fieldDataTypes);
                insertValues.append(fieldValule);
            }
            String insertSQL = "insert into "+modelName+" values("+insertValues.toString()+")";
            jdbcTemplate.execute(insertSQL);
            return 1;
        }catch(Exception e){
            e.printStackTrace();
            log.error(e.getMessage());
            return 0;
        }
    }

    /**
     * 删除模型数据
     *
     * @param etlModelData
     * @param modelName
     * @return
     */
    @Override
    public int deleteEtlModelData(JSONObject etlModelData, String modelName, JSONArray fields ) {
        try{
            StringBuffer delSQL = new StringBuffer("delete "+modelName+" where ");
            Map<String,String> fieldDataTypes = getDataTypesByFieldArray(fields);
            Iterator<String> keys = etlModelData.keySet().iterator();
            StringBuffer whereStr = new StringBuffer();
            while(keys.hasNext()){
                String fieldKey = keys.next();
                if(whereStr.length()>0){
                    whereStr.append(" and "+fieldKey+"="+getFieldValue(fieldKey,etlModelData.getString(fieldKey),fieldDataTypes));
                }else{
                    whereStr.append(fieldKey+"="+getFieldValue(fieldKey,etlModelData.getString(fieldKey),fieldDataTypes));
                }
            }
            delSQL.append(whereStr);
            jdbcTemplate.execute(delSQL.toString());
            return 1;
        }catch(Exception e){
            e.printStackTrace();
            log.error(e.getMessage());
            return 0;
        }
    }

    /**
     * 修改模型数据
     *
     * @param newModelData
     * @param oldModelData
     * @param modelName
     * @param fields
     * @return
     */
    @Override
    public int updateEtlModelData(JSONObject newModelData,JSONObject oldModelData, String modelName, JSONArray fields) {
        try{
            StringBuffer updateSQL = new StringBuffer("update "+modelName+" set ");
            Map<String,String> fieldDataTypes = getDataTypesByFieldArray(fields);
            Iterator<String> keys = newModelData.keySet().iterator();
            StringBuffer whereStr = new StringBuffer();
            StringBuffer setStr = new StringBuffer();
            while(keys.hasNext()){
                String fieldKey = keys.next();
                if(whereStr.length()>0){
                    whereStr.append(" and "+fieldKey+"="+getFieldValue(fieldKey,oldModelData.getString(fieldKey),fieldDataTypes));
                    setStr.append(","+fieldKey+"="+getFieldValue(fieldKey,newModelData.getString(fieldKey),fieldDataTypes));
                }else{
                    whereStr.append(fieldKey+"="+getFieldValue(fieldKey,oldModelData.getString(fieldKey),fieldDataTypes));
                    setStr.append(fieldKey+"="+getFieldValue(fieldKey,newModelData.getString(fieldKey),fieldDataTypes));
                }
            }
            updateSQL.append(setStr);
            updateSQL.append(" where ");
            updateSQL.append(whereStr);
            jdbcTemplate.execute(updateSQL.toString());
            return 1;
        }catch(Exception e){
            e.printStackTrace();
            log.error(e.getMessage());
            return 0;
        }
    }


    /**
     * 备份之前的历史表(依次为xxxxtable_1、xxxxxtable_2。。。)
     */
    public String backupOldTable(String tableName)throws Exception{
        String selTableSQL = "show tables like '"+tableName+"*'";
        List<Map<String,Object>> res = jdbcTemplate.queryForList(selTableSQL);
        Iterator<Map<String,Object>> rows = res.iterator();
        Boolean tableExisted = false;
        Integer maxPostfixNum = 0;
        while(rows.hasNext()){
            Map<String,Object> row = rows.next();
            String oldTableName = row.get("tab_name").toString();
            if((!tableExisted)&&(tableName.equals(oldTableName))){
                tableExisted = true;
            }
            if(oldTableName.indexOf(tableName+"_") >= 0){
                String postfix = oldTableName.replaceAll(tableName+"_","");
                try{
                    Integer postfixNum = Integer.parseInt(postfix);
                    if(maxPostfixNum<postfixNum){
                        maxPostfixNum = postfixNum;
                    }
                }catch(Exception e){
                    log.info("存在不规范备份表名："+oldTableName);
                  //  System.out.println("存在不规范备份表名："+oldTableName);
                }
            }
        }
        if(tableExisted){
            StringBuffer backupTableName = new StringBuffer(tableName);
            backupTableName.append("_").append(maxPostfixNum+1);
            String createTableSQL = "ALTER TABLE "+tableName+" RENAME TO "+backupTableName;
            log.info("重命名模型语句："+createTableSQL);
            jdbcTemplate.execute(createTableSQL);
            log.info("重命名模型语句："+createTableSQL+"；执行成功！");
            return backupTableName.toString();
        }else{
            log.info("表不存在，直接创建！");
            return "";
        }
    }

    public String getCreateTableSQLByModle(EtlModel etlModel){
        StringBuffer resSQL = new StringBuffer("CREATE  TABLE `"+etlModel.getModelName()+"`(");
        EtlModelFields condition = new EtlModelFields();
        condition.setModelId(etlModel.getId());
        List<EtlModelFields> fieldList = etlModelFieldsMapper.selectEtlModelFieldsList(condition);
        Iterator<EtlModelFields> rows = fieldList.iterator();
        StringBuffer fieldsListSQL = new StringBuffer();
        while(rows.hasNext()){
            EtlModelFields row = rows.next();
            StringBuffer fieldSQL = getCreateFieldSQL(row);
            if(fieldsListSQL.length()==0){
                fieldsListSQL.append(fieldSQL);
            }else{
                fieldsListSQL.append(",");
                fieldsListSQL.append(fieldSQL);
            }
        }
        fieldsListSQL.append(")");
        fieldsListSQL.append(" COMMENT '" + etlModel.getDescription() + "' row format delimited fields terminated by ','");
        resSQL.append(fieldsListSQL);
        return resSQL.toString();
    }

    public StringBuffer getCreateFieldSQL(EtlModelFields etlModelFields){
        StringBuffer resFieldSQL = new StringBuffer();
        String fieldName = etlModelFields.getFieldName();
        String dataType = etlModelFields.getDataType();
        Long length = etlModelFields.getDataLength();
        return resFieldSQL.append("`").append(fieldName).append("` ").append(dataType);
    }


}
